package org.example.dao.impl;


import org.example.dao.EmployeeDao;
import org.example.entity.Employee;
import org.example.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


public class EmployeeDaoImpl implements EmployeeDao {


    @Override
    public int count() {
        Connection connection = JdbcUtils.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "select count(*) as num from employee;";
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                return resultSet.getInt("num");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }


    @Override
    public List<Employee> queryAll() {
        List<Employee> employees = new ArrayList<>();
        // 连接
        Connection connection = JdbcUtils.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "select id,name,gender,birthday,wedlock from employee;";
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Employee employee = query(resultSet);
                employees.add(employee);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return employees;
    }


    @Override
    public List<Employee> queryPart(int num1, int num2) {
        List<Employee> employees = new ArrayList<>();
        Connection connection = JdbcUtils.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "select id,name,gender,birthday,wedlock from employee order by id limit ?,?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, num1);
            preparedStatement.setInt(2, num2);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Employee employee = query(resultSet);
                employees.add(employee);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.closeAll(connection, preparedStatement, resultSet);
        }

        return employees;
    }


    @Override
    public int addEmployee(Employee employee) {
        Connection connection = JdbcUtils.getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "insert into employee(name,gender,birthday,wedlock) values(?,?,?,?);";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, employee.getName());
            preparedStatement.setString(2, employee.getGender());
            preparedStatement.setObject(3, employee.getBirthday());
            preparedStatement.setString(4, employee.getWedlock());
            int i = preparedStatement.executeUpdate();
            return i;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.closeAll(connection, preparedStatement, null);
        }
        return 0;
    }


    @Override
    public int updateEmployee(Employee employee) {
        Connection connection = JdbcUtils.getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "update employee set name=?,gender=?,birthday=?,wedlock=? where id=?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, employee.getName());
            preparedStatement.setString(2, employee.getGender());
            preparedStatement.setObject(3, employee.getBirthday());
            preparedStatement.setString(4, employee.getWedlock());
            preparedStatement.setInt(5, employee.getId());
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.closeAll(connection, preparedStatement, null);
        }
        return 0;
    }


    @Override
    public int deleteEmployee(int id) {
        Connection connection = JdbcUtils.getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "delete from empsalary where eid =?";
        try {
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            preparedStatement.executeUpdate();
            String sql1 = "delete from employee where id=?";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setInt(1, id);
            int j = preparedStatement.executeUpdate();
            return j;
        } catch (SQLException throwables) {
            try {
                connection.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
        } finally {
            try {
                connection.commit();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            JdbcUtils.closeAll(connection, preparedStatement, null);
        }
        return 0;
    }


    @Override
    public Employee queryEmployee(int id) {
        Connection connection = JdbcUtils.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Employee employee = null;
        String sql = "select id,name,gender,birthday,wedlock from employee where id = ?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                employee = query(resultSet);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.closeAll(connection, preparedStatement, resultSet);
        }
        return employee;
    }


    private Employee query(ResultSet resultSet) throws SQLException {
        int id = resultSet.getInt("id");
        String name = resultSet.getString("name");
        String gender = resultSet.getString("gender");
        Date birthday = (Date) resultSet.getObject("birthday");
        String wedlock = resultSet.getString("wedlock");
        Employee employee = new Employee(id, name, gender, birthday, wedlock);
        return employee;
    }
}
